VERSION 5.00
Begin VB.Form frmInsurance 
   BorderStyle     =   1  'Fixed Single
   Caption         =   "Insurance"
   ClientHeight    =   3015
   ClientLeft      =   45
   ClientTop       =   330
   ClientWidth     =   5775
   LinkTopic       =   "Form2"
   MaxButton       =   0   'False
   MinButton       =   0   'False
   ScaleHeight     =   3015
   ScaleWidth      =   5775
   StartUpPosition =   3  'Windows Default
   Begin VB.ComboBox cmbSEL_INS_NAME 
      Height          =   315
      Left            =   1440
      Style           =   2  'Dropdown List
      TabIndex        =   0
      Top             =   120
      Width           =   2295
   End
   Begin VB.TextBox txtINS_FAX 
      Height          =   285
      Left            =   1440
      TabIndex        =   7
      Text            =   "INS_FAX"
      Top             =   2160
      Width           =   4215
   End
   Begin VB.TextBox txtINS_VAT_NO 
      Height          =   285
      Left            =   1440
      TabIndex        =   6
      Text            =   "INS_VAT_NO"
      Top             =   1800
      Width           =   4215
   End
   Begin VB.TextBox txtINS_REG 
      Height          =   285
      Left            =   1440
      TabIndex        =   5
      Text            =   "INS_REG"
      Top             =   1440
      Width           =   4215
   End
   Begin VB.CommandButton cmdDelete 
      Caption         =   "&Delete"
      Height          =   375
      Left            =   3720
      TabIndex        =   8
      Top             =   2520
      Visible         =   0   'False
      Width           =   855
   End
   Begin VB.CommandButton cmdNew 
      Caption         =   "&New"
      Height          =   375
      Left            =   4800
      TabIndex        =   2
      Top             =   120
      Width           =   855
   End
   Begin VB.TextBox txtINS_TEL 
      Height          =   285
      Left            =   1440
      TabIndex        =   4
      Text            =   "INS_TEL"
      Top             =   1080
      Width           =   4215
   End
   Begin VB.TextBox txtINS_NAME 
      Height          =   285
      Left            =   1440
      TabIndex        =   3
      Text            =   "INS_NAME"
      Top             =   720
      Width           =   4215
   End
   Begin VB.CommandButton cmdSelect 
      Caption         =   "&Select"
      Height          =   375
      Left            =   3840
      TabIndex        =   1
      Top             =   120
      Width           =   855
   End
   Begin VB.CommandButton cmdUpdate 
      Caption         =   "&Update"
      Height          =   375
      Left            =   4800
      TabIndex        =   9
      Top             =   2520
      Width           =   855
   End
   Begin VB.Label Label6 
      Alignment       =   1  'Right Justify
      Caption         =   "Insurance FAX"
      Height          =   255
      Left            =   120
      TabIndex        =   15
      Top             =   2160
      Width           =   1215
   End
   Begin VB.Label Label5 
      Alignment       =   1  'Right Justify
      Caption         =   "Insurance VAT"
      Height          =   255
      Left            =   120
      TabIndex        =   14
      Top             =   1800
      Width           =   1215
   End
   Begin VB.Label Label4 
      Alignment       =   1  'Right Justify
      Caption         =   "Insurance Reg"
      Height          =   255
      Left            =   120
      TabIndex        =   13
      Top             =   1440
      Width           =   1215
   End
   Begin VB.Label Label3 
      Alignment       =   1  'Right Justify
      Caption         =   "Insurance Tel"
      Height          =   255
      Left            =   120
      TabIndex        =   12
      Top             =   1080
      Width           =   1215
   End
   Begin VB.Label Label2 
      Alignment       =   1  'Right Justify
      Caption         =   "Insurance Name"
      Height          =   255
      Left            =   120
      TabIndex        =   11
      Top             =   720
      Width           =   1215
   End
   Begin VB.Label Label1 
      Alignment       =   1  'Right Justify
      Caption         =   "Insurance Name"
      Height          =   255
      Left            =   120
      TabIndex        =   10
      Top             =   120
      Width           =   1215
   End
   Begin VB.Line Line1 
      X1              =   120
      X2              =   5640
      Y1              =   600
      Y2              =   600
   End
End
Attribute VB_Name = "frmInsurance"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit
'==================================================================================
'Copyright (c) 2003, Andrew Jeremy Gargan (ajg@osite.co.za), Terence Leslie Van Zyl (tvz@osite.co.za)
'
'All rights reserved.
'
'Redistribution and use in source and binary forms, with or without modification,
'are permitted provided that the following conditions are met:
'
'Redistributions of source code must retain the above copyright notice, this list
'of conditions and the following disclaimer.
'
'Redistributions in binary form must reproduce the above copyright notice, this
'list of conditions and the following disclaimer in the documentation and/or other
'materials provided with the distribution.
'
'Neither the name of the <ORGANIZATION> nor the names of its contributors may be
'used to endorse or promote products derived from this software without specific
'prior written permission.
'
'THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
'ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
'WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
'IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
'INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
'BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 
'DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
'LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
'OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
'OF THE POSSIBILITY OF SUCH DAMAGE.
'===================================================================================

Dim mvarINS_KEY As String

Private Sub cmbSEL_INS_NAME_Click()
    Dim lvarControl As Control
    Dim lvarRS0 As DAO.Recordset
    'Populate the fields
    Set lvarRS0 = gvarDatabase.OpenRecordset("SELECT * FROM INSURANCE WHERE INS_KEY=" & cmbSEL_INS_NAME.ItemData(cmbSEL_INS_NAME.ListIndex), dbOpenForwardOnly)
    
    For Each lvarControl In Controls
            If TypeName(lvarControl) = "TextBox" And UCase(lvarControl.Name) Like "???INS_*" Then
                'MsgBox Mid(lvarControl.Name, 4)
                lvarControl.Text = IIf(IsNull(lvarRS0(Mid(lvarControl.Name, 4))), "", lvarRS0(Mid(lvarControl.Name, 4)))
            ElseIf TypeName(lvarControl) = "CheckBox" And UCase(lvarControl.Name) Like "???INS_*" Then
                'MsgBox Mid(lvarControl.Name, 4)
                lvarControl.Value = IIf(lvarRS0(Mid(lvarControl.Name, 4)) = 0, 0, 1)
            ElseIf TypeName(lvarControl) = "ComboBox" And UCase(lvarControl.Name) Like "???FOR_*" Then
                'TODO:look at tag for table and field to display
                'build list KEY FROM Control Name
                'if selected select
            ElseIf TypeName(lvarControl) = "Image" Then
                If IsNull(lvarRS0(Mid(lvarControl.Name, 4))) Then
                    Set lvarControl.Picture = LoadPicture
                Else
                    If lvarRS0(Mid(lvarControl.Name, 4)) <> "" Then
                        'TODO: Must get this to find local picture (only name in field) / just gened name
                        'Set lvarControl.Picture = LoadPicture(lvarRS0(Mid(lvarControl.Name, 4)))
                    Else
                        Set lvarControl.Picture = LoadPicture
                    End If
                End If
            ElseIf TypeName(lvarControl) = "CommandButton" Then
            End If
    Next
    'Set form key
    mvarINS_KEY = CStr(cmbSEL_INS_NAME.ItemData(cmbSEL_INS_NAME.ListIndex))
End Sub

Private Sub cmbSEL_INS_NAME_GotFocus()
    'cmbSEL_INS_NAME.Height = 1350
End Sub

Private Sub cmbSEL_INS_NAME_LostFocus()
    'cmbSEL_INS_NAME.Height = 350
    
    'If we did not have a match we do now
    If cmbSEL_INS_NAME.Text <> cmbSEL_INS_NAME.List(cmbSEL_INS_NAME.ListIndex) Then
        cmbSEL_INS_NAME.ListIndex = cmbSEL_INS_NAME.TopIndex
        'This then calls the list Click event on which the for gets populated :)
    End If
End Sub

Private Sub delete()
    Dim lvarSQL As String
    If IsNumeric(mvarINS_KEY) Then
        lvarSQL = "DELETE FROM INSURANCE WHERE INS_KEY =" & mvarINS_KEY
        On Error GoTo errorhandler 'Error handling start
        DBEngine.BeginTrans
        gvarDatabase.Execute lvarSQL
        DBEngine.CommitTrans
    
        On Error GoTo 0 'Return to normal execution
        
        MsgBox "Success Deleting Insurance INSURANCE"
        'reset the form data to reflect the deletion
        clear_form
        Fill_Form
    Else
        MsgBox "Please select an Insurance INSURANCE to delete?"
    End If
    Exit Sub
errorhandler:
    DBEngine.Rollback
    MsgBox DBEngine.Errors(0).Number & " - " & DBEngine.Errors(0).Description
    On Error GoTo 0
    
End Sub

Private Sub cmdDelete_Click()
    delete
End Sub

Private Sub cmdNew_Click()
    Dim lvarControl As Control
    cmbSEL_INS_NAME.Enabled = False
    cmdUpdate.Caption = "&Add"
    
    For Each lvarControl In Controls
        'MsgBox lvarControl.Name
        If TypeName(lvarControl) = "TextBox" And Trim(UCase(lvarControl.Name)) Like "TXTINS*" Then
            lvarControl.Text = ""
        End If
        If TypeName(lvarControl) = "CheckBox" And Trim(UCase(lvarControl.Name)) Like "CHKINS*" Then
            lvarControl.Value = 0
        End If
        'TODO: Dont have these and never will for this window
        'If TypeName(lvarControl) = "" And Trim(UCase(lvarControl.Name)) Like "CMBFOR*" Then
        '    lvarQuery = lvarQuery & Mid(lvarControl.Name, 4) & "=""" & Replace(lvarControl.Text, """", """""") & ""","
        'End If
    Next
End Sub

Private Sub cmdSelect_Click()
    cmbSEL_INS_NAME.Enabled = True
    
    'If we did not have a match we do now
    If cmbSEL_INS_NAME.Text <> cmbSEL_INS_NAME.List(cmbSEL_INS_NAME.ListIndex) Then
        cmbSEL_INS_NAME.ListIndex = cmbSEL_INS_NAME.TopIndex
        'This then calls the list Click event on which the for gets populated :)
    Else
        If (cmdUpdate.Caption = "&Add") Then
            cmbSEL_INS_NAME_Click
        End If
    End If
    cmdUpdate.Caption = "&Update"
End Sub

Private Sub cmdUpdate_Click()
    If (cmdUpdate.Caption = "&Update") Then
        update
    Else
        Add
    End If
End Sub

Private Sub Form_Load()
    'Initialise form key
    mvarINS_KEY = ""
    
    clear_form
    Fill_Form
End Sub

Private Sub Add()
    Dim lvarControl As Control
    Dim lvarQuery As String
    Dim lvarQuery2 As String
    Dim lvarFieldStr As String
    Dim lvarValueStr As String
    Dim lvarFields() As String
    Dim lvarValues() As String
    Dim lvarCounter As Integer
    
    lvarQuery = "INSERT INTO INSURANCE "
    
    lvarCounter = 0
    
    For Each lvarControl In Controls
        'MsgBox lvarControl.Name
        If Trim(UCase(lvarControl.Name)) Like "TXTINS*" Then
            ReDim Preserve lvarFields(lvarCounter)
            ReDim Preserve lvarValues(lvarCounter)
            
            'Add Field Name
            lvarFields(UBound(lvarFields)) = Mid(lvarControl.Name, 4)
            'Add Value
            If TypeName(lvarControl) = "TextBox" Then
                lvarValues(UBound(lvarValues)) = """" & Replace(lvarControl.Text, """", """""") & """"
            End If
            If TypeName(lvarControl) = "CheckBox" And Trim(UCase(lvarControl.Name)) Like "CHKINS*" Then
                If lvarControl.Value = 1 Then
                    lvarValues(UBound(lvarValues)) = "1"
                Else
                    lvarValues(UBound(lvarValues)) = "0"
                End If
            End If
        
            'TODO: Dont have these and never will for this window
            'If TypeName(lvarControl) = "" And Trim(UCase(lvarControl.Name)) Like "CMBFOR*" Then
            '    lvarQuery = lvarQuery & Mid(lvarControl.Name, 4) & "=""" & Replace(lvarControl.Text, """", """""") & ""","
            'End If
            
            'increment position
            lvarCounter = lvarCounter + 1
        End If
    Next
    
    lvarFieldStr = Join(lvarFields, ",")
    lvarValueStr = Join(lvarValues, ",")
    
    lvarQuery = lvarQuery & "(" & lvarFieldStr & ") VALUES (" & lvarValueStr & ");"
    lvarQuery2 = "SELECT MAX(INS_KEY) AS THE_KEY FROM INSURANCE"
    
    On Error GoTo errorhandler
    DBEngine.BeginTrans
    gvarDatabase.Execute lvarQuery
    mvarINS_KEY = gvarDatabase.OpenRecordset(lvarQuery2)("THE_KEY")
    DBEngine.CommitTrans
    
    MsgBox "Success Adding Insurance INSURANCE"
    
    'So we now rebuild the form to show our addition
    clear_form
    Fill_Form
    Exit Sub
errorhandler:
    DBEngine.Rollback
    MsgBox DBEngine.Errors(0).Number & " - " & DBEngine.Errors(0).Description
    On Error GoTo 0
End Sub

Private Sub update()
    Dim lvarControl As Control
    Dim lvarQuery As String
    
    lvarQuery = "UPDATE INSURANCE SET "
    
    For Each lvarControl In Controls
        'MsgBox lvarControl.Name
        If TypeName(lvarControl) = "TextBox" And Trim(UCase(lvarControl.Name)) Like "TXTINS*" Then
            lvarQuery = lvarQuery & Mid(lvarControl.Name, 4) & "=""" & Replace(lvarControl.Text, """", """""") & ""","
        End If
        If TypeName(lvarControl) = "CheckBox" And Trim(UCase(lvarControl.Name)) Like "CHKINS*" Then
            If lvarControl.Value = 1 Then
                lvarQuery = lvarQuery & Mid(lvarControl.Name, 4) & "=1,"
            Else
                lvarQuery = lvarQuery & Mid(lvarControl.Name, 4) & "=0,"
            End If
        End If
        'TODO: Dont have these and never will for this window
        'If TypeName(lvarControl) = "" And Trim(UCase(lvarControl.Name)) Like "CMBFOR*" Then
        '    lvarQuery = lvarQuery & Mid(lvarControl.Name, 4) & "=""" & Replace(lvarControl.Text, """", """""") & ""","
        'End If
    Next
    
    lvarQuery = Left(lvarQuery, Len(lvarQuery) - 1) & " WHERE INS_KEY = " & cmbSEL_INS_NAME.ItemData(cmbSEL_INS_NAME.ListIndex)
    
    On Error GoTo errorhandler
    DBEngine.BeginTrans
    gvarDatabase.Execute lvarQuery
    DBEngine.CommitTrans
    
    MsgBox "Success Updating Insurance INSURANCE's Details"
    Exit Sub
errorhandler:
    DBEngine.Rollback
    MsgBox DBEngine.Errors(0).Number & " - " & DBEngine.Errors(0).Description
    On Error GoTo 0
End Sub

Private Sub Fill_Form()
    Dim lvarRS As DAO.Recordset
    Dim lvarControl As Control
    Dim lvarPos As Long
    'DBEngine.BeginTrans
    Set lvarRS = gvarDatabase.OpenRecordset("SELECT * FROM INSURANCE ORDER BY INS_NAME", dbOpenForwardOnly)
    
    'Set Buttons Accordingly
    cmbSEL_INS_NAME.Enabled = True
    cmdUpdate.Caption = "&Update"
    
    If lvarRS.EOF Then
        'InValid
        'stsBar.SimpleText = "Please select a Valid Assessment"
        'Cancel = True
    Else
        lvarPos = 0
        Do While Not lvarRS.EOF
            
            cmbSEL_INS_NAME.AddItem lvarRS("INS_NAME")
            cmbSEL_INS_NAME.ItemData(cmbSEL_INS_NAME.NewIndex) = lvarRS("INS_KEY")
            If CStr(lvarRS("INS_KEY")) = mvarINS_KEY Then
                lvarPos = cmbSEL_INS_NAME.NewIndex
            End If
            lvarRS.MoveNext
        Loop
        cmbSEL_INS_NAME.ListIndex = lvarPos
    End If
End Sub

Private Sub clear_form()
    Dim lvarControl As Control
        For Each lvarControl In Controls
            If TypeName(lvarControl) = "TextBox" Then
                lvarControl.Text = ""
            ElseIf TypeName(lvarControl) = "CheckBox" Then
                lvarControl.Value = 0
            'ElseIf TypeName(lvarControl) = "ComboBox" And lvarControl.Tag <> "" Then
            '    lvarControl.Clear
            ElseIf TypeName(lvarControl) = "ComboBox" Then
                lvarControl.Clear
            ElseIf TypeName(lvarControl) = "ListBox" Then
                lvarControl.Clear
            ElseIf TypeName(lvarControl) = "CommandButton" Then
            End If
        Next
        'MsgBox "kkk"
End Sub



